package gwtappcontainer.server.apps.insight;
import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Practise;
import gwtappcontainer.shared.apps.insight.ProgramType;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
public class ProgramTypeRepository {
public static ProgramType get(String programTypeName) {
try {
String sql = "select program_type_id, program_type from program_types where program_type = ?";
ProgramType programType = null;
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, programTypeName.toLowerCase());
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
programType = new ProgramType();
programType.id = resultSet.getInt(1);
programType.name = resultSet.getString(2);
}
}
}
if (null == programType)
return null;
sql = "select practise from practises where practise_id in (select practise_id from program_type_practises where program_type_id = ?) order by practise";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, programType.id);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
programType.practises.add(resultSet.getString(1));
}
}
}
return programType;
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static ProgramType add(String programTypeName) {
if (null != get(programTypeName))
throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS,
"Program type [" + programTypeName + "] already exists");
try {
String sql = "insert into program_types values (0, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, programTypeName.toLowerCase());
ps.executeUpdate();
}
}
ProgramType programType = get(programTypeName);
return programType;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void delete(String programTypeName) {
ProgramType programType = get(programTypeName);
if (null == programType)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"Program type [" + programTypeName + "] does not exist");
try {
String sql = "delete from program_types where program_type = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, programTypeName.toLowerCase());
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static ArrayList<ProgramType> getAll() {
try {
String sql = "select program_type_id, program_type from program_types order by program_type";
ArrayList<ProgramType> all = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
ProgramType programType = new ProgramType();
programType.id = resultSet.getInt(1);
programType.name = resultSet.getString(2);
all.add(programType);
}
}
}
}
return all;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static ProgramType addPractise(String programTypeName, String practiseName) {
ProgramType programType = get(programTypeName);
if (null == programType)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"Program type [" + programTypeName + "] does not exist");
Practise practise = PractiseRepository.get(practiseName);
if (null == practise)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"Practise [" + practiseName + "] does not exist");
practiseName = practiseName.toLowerCase();
if (programType.practises.contains(practiseName))
return programType;
try {
String sql = "insert into program_type_practises values (?, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, programType.id);
ps.setLong(2, practise.id);
ps.executeUpdate();
}
}
programType.practises.add(practiseName.toLowerCase());
Collections.sort(programType.practises);
return programType;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static ProgramType deletePractise(String programTypeName, String practiseName) {
ProgramType programType = get(programTypeName);
if (null == programType)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"Program type [" + programTypeName + "] does not exist");
Practise practise = PractiseRepository.get(practiseName);
if (null == practise)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"Practise [" + practiseName + "] does not exist");
practiseName = practiseName.toLowerCase();
if (! programType.practises.contains(practiseName))
return programType;
try {
String sql = "delete from program_type_practises where program_type_id = ? and practise_id = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, programType.id);
ps.setLong(2, practise.id);
ps.executeUpdate();
}
}
programType.practises.remove(practiseName.toLowerCase());
return programType;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
}